package com.xxxcompany.world.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.xxxcompany.utils.DbUtils;
import com.xxxcompany.world.dao.ICountryDAO;
import com.xxxcompany.world.entity.Country;

public class CountryDAOImpl implements ICountryDAO {

	@Override
	public List<Country> findAll() throws SQLException {

		List<Country> list = new ArrayList<>();
		// 1. 获取连接
		Connection conn = DbUtils.getConnection();
		// 2. 执行查询
		String sql = "select * from country";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		// 3. 遍历结果集，封装结果信息
		while (rs.next()) {
			String code = rs.getString(1);
			String name = rs.getString(2);
			String continent = rs.getString(3);
			Country c = new Country(code, name, continent);
			list.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);
		return list;
	}

	@Override
	public List<Country> find(int start, int count) throws SQLException {
		// TODO Auto-generated method stub
		// 1. 获取连接
		Connection conn = DbUtils.getConnection();
		// 2. 得到查询对象
		Statement stmt = conn.createStatement();
		// 3. 查询
		// limit 开始位置,数量
		String sql = "select * from country limit " + start + "," + count;
		ResultSet rs = stmt.executeQuery(sql);
		// 4. 遍历查询结果
		List<Country> result = new ArrayList<Country>();
		while (rs.next()) {
			String code = rs.getString("code");
			String name = rs.getString("name");
			String continent = rs.getString("continent");

			Country c = new Country(code, name, continent);
			result.add(c);
		}
		// 5. 关闭连接，释放资源
		DbUtils.destoryResource(conn, stmt, rs);

		return result;
	}

	@Override
	public int getCount() throws SQLException {
		// TODO Auto-generated method stub
		// 1. 获取连接
		Connection conn = DbUtils.getConnection();
		// 2. 得到查询对象
		Statement stmt = conn.createStatement();
		// 3. 查询
		String sql = "select count(*) from country";
		ResultSet rs = stmt.executeQuery(sql);
		// 4. 遍历查询结果
		int count = 0;
		if (rs.next()) {
			count = rs.getInt(1);

		}
		// 5. 关闭连接，释放资源
		DbUtils.destoryResource(conn, stmt, rs);

		return count;
	}

}